﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
using System.Data;

namespace GeuzenAdministratie
{
    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            server = GeuzenAdministratie.Properties.Settings.Default.dbServer; //server, lokaal, internet of netwerk
            database = GeuzenAdministratie.Properties.Settings.Default.dbName; //databasenaam
            uid = GeuzenAdministratie.Properties.Settings.Default.dbUser; //gebruikersnaam
            password = GeuzenAdministratie.Properties.Settings.Default.dbPass; //wachtwoord
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        new Error("Cannot connect to server.  Contact administrator",30);
                        break;

                    case 1045:
                        new Error("Verkeerde gebruikersnaam/wachtwoord database. Contacteer administrator.",30);
                        break;
                    default:
                        new Error("problemen met verbinding database",30);
                        break;
                }
                
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //Insert new member
        public void Insert(string initial, string tussenvoegsel, string lastName, string KNSA)
        {
            string query;
            //TODO: Afhandeling lege strings (Behalve tussenvoegsel)
            if (tussenvoegsel != null)
            {
                query = "INSERT INTO members (initial, tussenvoegsel, lastName, KNSA) VALUES ('" + initial + "','" + tussenvoegsel + "','" + lastName + "','" + KNSA + "')";
            }
            else
            {
                query = "INSERT INTO members (initial, lastName, KNSA) VALUES ('" + initial + "','" + lastName + "','" + KNSA + "')";
            }
            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Update statement
        public void Update(string initial, string tussenvoegsel, string lastName, string KNSA, int member_id, string oldKNSA)
        {
            string query = "UPDATE members, presence SET members.initial='" + initial + "', members.tussenvoegsel='" + tussenvoegsel + "', members.lastName='" + lastName + "', members.KNSA='" + KNSA + "',presence.KNSA='" + KNSA + "' WHERE members.member_id='" + member_id + "' AND presence.KNSA ='"+oldKNSA+"'";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = query;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Delete statement
        public void Delete(string member_id)
        {
            string query = "UPDATE members set deleted=1 WHERE members.member_id='" + member_id + "'";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

        //Select statement
        public DataTable Select()
        {
            string query = "SELECT * FROM members WHERE members.deleted=0";
            DataTable DT = new DataTable();

            //Open connection
            if (this.OpenConnection() == true)
            {
                MySqlDataAdapter DA = new MySqlDataAdapter();
                DA.SelectCommand = new MySqlCommand(query, connection);
                DA.Fill(DT);

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return DT;
            }
            else
            {
                return DT;
            }
        }

        public string getMemberNameByKNSA(string KNSA)
        {
            string query = "SELECT * FROM geuzen.members WHERE `KNSA` = '" + KNSA + "' LIMIT 1";
            {
                DataTable DT = new DataTable();
                string name = ""; 
                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);
                    Console.WriteLine(DT.ToString());

                    //close Connection
                    this.CloseConnection();
                    if (DT.Rows.Count == 0)
                    {
                        new Error("Onbekende gebruiker");
                    }
                    //return list to be displayed
                    name = DT.Rows[0]["initial"].ToString() +" "+ DT.Rows[0]["tussenvoegsel"].ToString() +" "+ DT.Rows[0]["lastNAme"].ToString();

                    return name;
                }
                else
                {
                    return name;
                }
            }
        }

        public DataTable getMemberByKNSA(string KNSA)
        {
            string query = "SELECT * FROM geuzen.members WHERE `KNSA` = '" + KNSA + "' LIMIT 1";
            {
                DataTable DT = new DataTable();

                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);
                    Console.WriteLine(DT.ToString());

                    //close Connection
                    this.CloseConnection();
                    if (DT.Rows.Count == 0)
                    {
                        new Error("Onbekende gebruiker");
                    }
                    //return list to be displayed
                    return DT;
                }
                else
                {
                    return DT;
                }
            }
        }

        public DataTable getCertainDate(DateTime date)
        {
            string onlydate = date.ToString("yyyy-MM-dd");
            string query = "SELECT DISTINCT presence.knsa, members.initial, members.tussenvoegsel, members.lastName, presence.checkin FROM presence, members WHERE DATE(presence.checkin)='" + onlydate + "' AND members.KNSA=presence.KNSA GROUP BY presence.KNSA";
            Console.WriteLine(query);
            {
                DataTable DT = new DataTable();

                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);
                    Console.WriteLine(DT.ToString());

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return DT;
                }
                else
                {
                    return DT;
                }
            }
        }

        public DataTable getOneMember(string member_id)
        {
            string query = "SELECT * FROM geuzen.members WHERE `member_id` = '" + member_id + "' LIMIT 1";
            {
                DataTable DT = new DataTable();

                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return DT;
                }
                else
                {
                    return DT;
                }
            }
        }
        public DataTable getPresence(string KNSA)//lijst met aanwezigheidsdata van user opvragen
        {
            string query = "SELECT checkin FROM geuzen.presence WHERE `KNSA` = '" + KNSA + "'"; //TIMESTAMP opvragen

            {
                DataTable DT = new DataTable();

                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return DT;
                }
                else
                {
                    return DT;
                }
            }

        }
        public DataTable getPresenceMonthCalendar(string KNSA)//lijst met aanwezigheidsdata van user opvragen
        {
            // string query = "SELECT checkin FROM geuzen.presence WHERE `KNSA` = '" + KNSA + "'"; //TIMESTAMP opvragen
            string query = "SELECT DATE_FORMAT(checkin, '%Y') AS year, DATE_FORMAT(checkin, '%m') AS month, DATE_FORMAT(checkin, '%d') AS day FROM geuzen.presence WHERE `KNSA` = '" + KNSA + "'";

            {
                DataTable DT = new DataTable();

                //Open connection
                if (this.OpenConnection() == true)
                {
                    MySqlDataAdapter DA = new MySqlDataAdapter();
                    DA.SelectCommand = new MySqlCommand(query, connection);
                    DA.Fill(DT);

                    //close Connection
                    this.CloseConnection();

                    //return list to be displayed
                    return DT;
                }
                else
                {
                    return DT;
                }
            }

        }

        public void setAanwezig(string KNSA)
        {
            string query = "INSERT INTO presence (KNSA) VALUES ('" + KNSA + "')";
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                Console.WriteLine(query);
                //close Connection
                this.CloseConnection();
            }
            else
            {
            }
        }

        public bool getAanwezigVandaag(string KNSA)
        //TODO: controle of user vandaag al heeft aangemeld.
        {
            DataTable DT = new DataTable();
            DateTime date = DateTime.Now;
            string onlydate = date.ToString("yyyy-MM-dd");
            string query = "SELECT presence.checkin FROM presence WHERE DATE(presence.checkin)='" + onlydate + "' AND presence.KNSA='" + KNSA + "'";
            if (this.OpenConnection() == true)
            {
                MySqlDataAdapter DA = new MySqlDataAdapter();
                DA.SelectCommand = new MySqlCommand(query, connection);
                DA.Fill(DT);
                Console.WriteLine(query);
                //close Connection
                this.CloseConnection();
                if (DT.Rows.Count >= 1)
                {
                    return true;
                }
                else { return false; }


            }
            else
            {
                MessageBox.Show("aanwezigheidsregistratie gefaald! - controleer of lid in database staat.");
                return true;
            }
        }

        //count number of results
        public int Count()
        {
            string query = "SELECT Count(*) FROM tableinfo";
            int Count = -1;

            //Open Connection
            if (this.OpenConnection() == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                //close Connection
                this.CloseConnection();

                return Count;
            }
            else
            {
                return Count;
            }
        }

        //Backup
        public void Backup()
        {
            try
            {
                DateTime Time = DateTime.Now;
                int year = Time.Year;
                int month = Time.Month;
                int day = Time.Day;
                int hour = Time.Hour;
                int minute = Time.Minute;
                int second = Time.Second;
                int millisecond = Time.Millisecond;

                //Save file to C:\ with the current date as a filename
                string path;
                path = "C:\\MySqlBackup" + year + "-" + month + "-" + day +
            "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
                StreamWriter file = new StreamWriter(path);


                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysqldump";
                psi.RedirectStandardInput = false;
                psi.RedirectStandardOutput = true;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
                    uid, password, server, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);

                string output;
                output = process.StandardOutput.ReadToEnd();
                file.WriteLine(output);
                process.WaitForExit();
                file.Close();
                process.Close();
            }
            catch (IOException ex)
            {
                MessageBox.Show("Error , unable to backup!");
            }
        }

        //Restore
        public void Restore()
        {
            try
            {
                //Read file from C:\
                string path;
                path = "C:\\MySqlBackup.sql";
                StreamReader file = new StreamReader(path);
                string input = file.ReadToEnd();
                file.Close();

                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysql";
                psi.RedirectStandardInput = true;
                psi.RedirectStandardOutput = false;
                psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
                    uid, password, server, database);
                psi.UseShellExecute = false;


                Process process = Process.Start(psi);
                process.StandardInput.WriteLine(input);
                process.StandardInput.Close();
                process.WaitForExit();
                process.Close();
            }
            catch (IOException ex)
            {
                MessageBox.Show("Error, kon de database niet restoren. Zorg er voor dat de database staat in C:\\MySqlBackup.sql");
            }
        }
    }
}
